<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" import="support.*, java.util.*,  java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
	<link rel="stylesheet" type="text/css" href="../resource/styleCSS.css" /> 	
	<link rel="stylesheet" type="text/css" href="../resource/columnCSS.css" /> 

	<title>Graduate School Application - Applications</title>
</head>
<body>
	<p class="sansserif">
	<div class="container">
	<div class="center_div">
		<%		
			try {
				//Registering Postgresql JDBC driver
				Class.forName("org.postgresql.Driver");
			} catch (ClassNotFoundException e) {
				System.err.println("ClassNotFoundException: " + e);
			} 
			try {	
				//Open a connection to the database
				Connection conn = DriverManager.getConnection(
						"jdbc:postgresql://localhost:5432/CSE135_SP11?"
						+ "user=postgres&password=postgres");
		
				conn.setAutoCommit(false);
	
				Statement stmt = conn.createStatement();			
				
				// Show the results by selecting the query based on the incoming parameters 
				ResultSet rs;
				if(request.getParameter("major") != null)
				{
					rs = stmt.executeQuery("SELECT DISTINCT s.first_name, s.middle_name," + 
								" s.last_name FROM Students s, Disciplines dp, " +
								"Degrees deg WHERE deg.student=s.id AND deg.discipline=dp.id" +
								" AND dp.discipline=\'"+ request.getParameter("major") + "\'");
				}
				else if(request.getParameter("spec") != null)	
				{
					rs = stmt.executeQuery("SELECT DISTINCT s.first_name, s.middle_name," + 
							" s.last_name FROM students s, " + 
							"Specializations sp WHERE s.specialization=sp.id AND " + 
							"sp.specialization=\'" + request.getParameter("spec") + "\'");
				}
				else
				{
					rs = stmt.executeQuery("SELECT * FROM students");
				}
	
				conn.commit();
				conn.setAutoCommit(true);
				
				int numOfApplicants = 0;
		%>
		
		<div class="colmask threecol">
		<div class="colmid">
		<div class="colleft">
			<div class="bodycol">
				<p><b>Application Results: </b></p>
			</div>
			<div class="sidecol">
				<p><a href="../invalidate.jsp">Logout</a></p>
			</div>
		</div>
		</div>
		</div>
		<div style="clear:both;"></div>	
		
		<ul>
		<% 	//Iteration code to print out all results
			while(rs.next()) {
				StringBuilder name = new StringBuilder();
				name.append(rs.getString("first_name"));
				name.append(" ");
				if(rs.getString("middle_name") != null)
				{
					name.append(rs.getString("middle_name"));
					name.append(" ");
				}
				name.append(rs.getString("last_name"));
				
				out.println("<li>" + name.toString() + "</li>");
			 
		 		numOfApplicants++;
		 	}
		
			// If no applicants present from query state so
			if(numOfApplicants == 0)
				out.println("<p>" +
							"No Applicants matched your search!</p>");
			rs.close();
			stmt.close();
			conn.close();
		
			} catch (SQLException e) {
				System.err.println("SQLException: " + e);
			}
		%>
		</ul>
	</div>
	</div>		
</body>
</html>